﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace UpdateDatabase
{
    class Program
    {
        static void Main(string[] args)
        {
            string origin = @"E:\temp\数据库更新\数据框架-20210413.xlsx";
            string updated = @"E:\temp\数据库更新\数据框架-20210413_updated.xlsx";

            //拷贝文件
            File.Delete(updated);
            File.Copy(origin, updated);

            //批号-上传日期
            Dictionary<string, DateTime> uploadingDates = new Dictionary<string, DateTime>();
            var files=Directory.GetFiles(@"E:\work\电性能数据");
            foreach(var file in files)
            {
                if (file.StartsWith(@"E:\work\电性能数据\统计数据"))
                {
                    var date = File.GetCreationTime(file);
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                    using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(file)))
                    {
                        var sheet = package.Workbook.Worksheets[0];                        
                        var lot = sheet.Cells[2, 1].Value;
                        if (lot != null)
                        {
                            uploadingDates[lot.ToString()] = date;
                        }
                    }
                }
            }

            //

            //
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(updated)))
            {
                var sheet = package.Workbook.Worksheets["SubstrateInfos"];


                //1）SchedulingDate (从1数起21列) 改为日期格式
                sheet.Column(21).Style.Numberformat.Format = "yyyy/m/d";

                //2）UploadingDate(从1数起25列) 改为日期格式
                sheet.Column(25).Style.Numberformat.Format = "yyyy/m/d";

                //3）SubstrateType->SubstrateMaterialType,所有"晶界层-已镀"->"晶界层"
                sheet.Cells[1, 23].Value = "SubstrateMaterialType";
                int row = 2;
                while (true)
                {

                    var mat_obj = sheet.Cells[row, 23].Value;//最小耐压
                    if (mat_obj == null)
                    {
                        break;
                    }

                    var mat_str = mat_obj.ToString();
                    if (mat_str.StartsWith("晶界层"))
                    {
                        sheet.Cells[row, 23].Value = "晶界层";
                    }
                    
                    row++;
                }

                //4) 耐压(增加在29列)
                sheet.Cells[1, 29].Value = "BDV";
                row = 2;
                while (true)
                {
                    
                    var workv_min = sheet.Cells[row,28].Value;//最小耐压
                    if (workv_min == null)
                    {
                        break;
                    }

                    double workv;
                    var success = double.TryParse(workv_min.ToString(), out workv);
                    if (success)
                    {
                        sheet.Cells[row, 29].Value = workv * 2.5;
                        
                    }
                    else
                    {
                        break;
                    }
                    row++;
                }


                //5) SubstrateType
                sheet.Cells[1, 30].Value = "SubstrateType";
                row = 2;
                while (true)
                {

                    var cpa = sheet.Cells[row, 3].Value;
                    if (cpa == null)
                    {
                        break;
                    }

                    double cpa_val = (double)cpa;

                    string mtype = sheet.Cells[row, 23].Value.ToString();

                    string type = "未分类";
                    if (mtype.StartsWith("K"))//CT
                    {
                        if (cpa_val >= 155 && cpa_val < 175)
                        {
                            type = "CT-A";
                        }
                        else if (cpa_val >= 175 && cpa_val < 185)
                        {
                            type = "CT-A;CT-B";
                        }
                        else if (cpa_val >= 185 && cpa_val < 205)
                        {
                            type = "CT-B";
                        }
                        
                    }
                    else if (mtype.StartsWith("晶界层"))
                    {
                        if (cpa_val >= 500 && cpa_val < 600)
                        {
                            type = "CS-A";
                        }
                        else if (cpa_val >= 600 && cpa_val < 760)
                        {
                            type = "CS-A;CS-B";
                        }
                        else if (cpa_val >= 760 && cpa_val < 800)
                        {
                            type = "CS-A;CS-B;CS-C";
                        }
                        else if (cpa_val >= 800 && cpa_val < 820)
                        {
                            type = "CS-B;CS-C";
                        }
                        else if (cpa_val >= 820 && cpa_val < 900)
                        {
                            type = "CS-C";
                        }
                        else if (cpa_val >= 1050 && cpa_val < 1150)
                        {
                            type = "CS-F";
                        }
                        else if (cpa_val >= 1150 && cpa_val < 1300)
                        {
                            type = "CS-D;CS-F";
                        }
                        else if (cpa_val >= 1300 && cpa_val < 1380)
                        {
                            type = "CS-D;CS-E;CS-F";
                        }
                        else if (cpa_val >= 1380 && cpa_val < 1850)
                        {
                            type = "CS-E;CS-F";
                        }
                        else if (cpa_val >= 1850 && cpa_val < 2100)
                        {
                            type = "CS-F";
                        }
                    }

                    sheet.Cells[row, 30].Value = type;

                    row++;
                }

                //6)UploadingDate
                //7)增加
                row = 2;
                sheet.Cells[1, 31].Value = "操作";
                while (true)
                {

                    var lot = sheet.Cells[row, 1].Value;//最小耐压
                    if (lot == null)
                    {
                        break;
                    }

                    //增加
                    sheet.Cells[row, 31].Value = "增加";

                    var uploadingdate= (double)sheet.Cells[row, 25].Value;
                    if (uploadingdate ==0)
                    {
                        if (uploadingDates.ContainsKey(lot.ToString()))
                        {
                            sheet.Cells[row, 25].Value = uploadingDates[lot.ToString()].Date;
                        }
                    }

                    row++;
                }


                //8)镀层厚度->镀层类型
                sheet.Cells[1, 17].Value = "PlatedType";
                row = 2;
                while (true)
                {

                    var pthick = sheet.Cells[row, 17].Value;//镀层厚度
                    if (pthick == null)
                    {
                        break;
                    }

                    double thick = (double)pthick;
                    if (thick >= 6)
                    {
                        sheet.Cells[row, 17].Value = "镀6um";
                    }
                    else if (thick >= 4)
                    {
                        sheet.Cells[row, 17].Value = "镀4um";
                    }

                    if (thick >= 2)
                    {
                        sheet.Cells[row, 17].Value = "镀2um";
                    }

                    if (thick >= 1)
                    {
                        sheet.Cells[row, 17].Value = "镀1um";
                    }
                    else
                    {
                        sheet.Cells[row, 17].Value = "溅射";
                    }




                    row++;
                }


                package.Save();

            }


        }
    }
}
